package com.ayf.payment.game.api.utils;

import com.ayf.payment.game.api.exception.ExcelException;
import com.ayf.payment.game.api.type.ExportType;
import com.swwx.charm.commons.lang.utils.LogPortal;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.lang.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xiaowei.wang on 2016/05/12.
 */
public class ExcelUtils {
    /**
     * @param lists     数据源
     * @param fieldMap  类的英文属性和Excel中的中文列名的对应关系
     *                  如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式
     *                  如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写
     *                  fieldMap.put("college.collegeName","学院名称")
     * @param sheetName 工作表的名称
     * @param sheetSize 每个工作表中记录的最大个数
     * @param response  导出流
     * @throws ExcelException
     * @MethodName : listToExcel
     * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小）
     */
    public static <T> String listToExcelZip(List<List<T>> lists, LinkedHashMap<String, String> fieldMap, String sheetName,
                                            int sheetSize, int breakSize, HttpServletResponse response,
                                            List appendSummaryList, Map<String, Exchange> exchange, ExportType exportType) throws ExcelException {
        String path = PrjCommon.getTempPath() + "/" + SnowFlakeUtils.getTraceId();
        if (sheetSize > 65535 || sheetSize < 1) {
            sheetSize = 65535;
        }
        File file = new File(path);
        if (!file.exists()) {
            file.mkdir(); //创建文件夹
        }
        //创建工作簿并发送到OutputStream指定的地方
        WritableWorkbook wwb;
        try {
//            wwb = Workbook.createWorkbook(out);
            //在服务器端创建文件夹
            int m = 1;
            for (List<T> list : lists) {
                int a = (m - 1) * breakSize + 1;
                int b = m == lists.size() ? (m - 1) * breakSize + list.size() : m * breakSize;
                wwb = Workbook.createWorkbook(new File(path + "/" + a + "-" + b + ".xls"));
                m += 1;
                //因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条
                //所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
                //1.计算一共有多少个工作表
                double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue());

                //2.创建相应的工作表，并向其中填充数据
                if (sheetNum == 0) {
                    //没有数据，只有表头
                    WritableSheet sheet = wwb.createSheet(sheetName, 1);
                    fillSheet(sheet, list, fieldMap, 0, list.size() - 1, appendSummaryList, null, exportType);
                } else {
                    for (int i = 0; i < sheetNum; i++) {
                        //如果只有一个工作表的情况
                        if (1 == sheetNum) {
                            WritableSheet sheet = wwb.createSheet(sheetName, i);
                            fillSheet(sheet, list, fieldMap, 0, list.size() - 1, appendSummaryList, exchange, exportType);
                            //有多个工作表的情况
                        } else {
                            WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);
                            //获取开始索引和结束索引
                            int firstIndex = i * sheetSize;
                            int lastIndex = Math.min((i + 1) * sheetSize - 1, list.size() - 1);
                            //填充工作表
                            fillSheet(sheet, list, fieldMap, firstIndex, lastIndex, appendSummaryList, exchange, exportType);
                        }
                    }
                }
                wwb.write();
                wwb.close();
            }
            return path;
        } catch (Exception e) {
            LogPortal.error("异常信息是:", e);
            //如果是ExcelException，则直接抛出
            if (e instanceof ExcelException) {
                throw (ExcelException) e;
                //否则将其它异常包装成ExcelException再抛出
            } else {
                throw new ExcelException("导出Excel失败");
            }
        }
    }

    /**
     * @param list      数据源
     * @param fieldMap  类的英文属性和Excel中的中文列名的对应关系
     * @param sheetSize 每个工作表中记录的最大个数
     * @param response  使用response可以导出到浏览器
     * @param breakSize 拆分大小
     * @throws ExcelException
     * @MethodName : listToExcel
     * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
     */
    public static <T> void listToExcelZip(List<List<T>> list, LinkedHashMap<String, String> fieldMap,
                                          String sheetName, int sheetSize, int breakSize, HttpServletResponse response,
                                          String fileName, List appendSummaryList, Map<String, Exchange> exchange, ExportType exportType) throws ExcelException {
        //设置默认文件名为当前时间：年月日时分秒
        if (StringUtils.isBlank(fileName)) {
            fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
        }
        try {
            String path = listToExcelZip(list, fieldMap, sheetName, sheetSize, breakSize, response, appendSummaryList, exchange, exportType);
            ZipUtils.downLoadFiles(path, fileName, response);
        } catch (Exception e) {
            LogPortal.error("异常信息是:", e);
            //如果是ExcelException，则直接抛出
            if (e instanceof ExcelException) {
                throw (ExcelException) e;
                //否则将其它异常包装成ExcelException再抛出
            } else {
                throw new ExcelException("导出Excel失败");
            }
        }
    }

    /**
     * @param fieldName 字段名
     * @param o         对象
     * @return 字段值
     * @MethodName : getFieldValueByName
     * @Description : 根据字段名获取字段值
     */
    private static Object getFieldValueByName(String fieldName, Object o) throws Exception {
        Object value = null;
        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null) {
            field.setAccessible(true);
            value = field.get(o);
        } else {
            throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }
        return value;
    }

    /**
     * @param fieldName 字段名
     * @param clazz     包含该字段的类
     * @return 字段
     * @MethodName : getFieldByName
     * @Description : 根据字段名获取字段
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        //拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();
        //如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }
        //否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }
        //如果本类和父类都没有，则返回空
        return null;
    }

    /**
     * @param fieldNameSequence 带路径的属性名或简单属性名
     * @param o                 对象
     * @return 属性值
     * @throws Exception
     * @MethodName : getFieldValueByNameSequence
     * @Description :
     * 根据带路径或不带路径的属性名获取属性值
     * 即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等
     */
    private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {
        Object value = null;
        //将fieldNameSequence进行拆分
        String[] attributes = fieldNameSequence.split("\\.");
        if (attributes.length == 1) {
            value = getFieldValueByName(fieldNameSequence, o);
        } else {
            //根据属性名获取属性对象
            Object fieldObj = getFieldValueByName(attributes[0], o);
            String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
            value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
        }
        return value;
    }

    /**
     * @param fieldName  字段名
     * @param fieldValue 字段值
     * @param o          对象
     * @MethodName : setFieldValueByName
     * @Description : 根据字段名给对象的字段赋值
     */
    private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null) {
            field.setAccessible(true);
            //获取字段类型
            Class<?> fieldType = field.getType();
            //根据字段类型给字段赋值
            if (String.class == fieldType) {
                field.set(o, String.valueOf(fieldValue));
            } else if ((Integer.TYPE == fieldType)
                    || (Integer.class == fieldType)) {
                field.set(o, Integer.parseInt(fieldValue.toString()));
            } else if ((Long.TYPE == fieldType)
                    || (Long.class == fieldType)) {
                field.set(o, Long.valueOf(fieldValue.toString()));
            } else if ((Float.TYPE == fieldType)
                    || (Float.class == fieldType)) {
                field.set(o, Float.valueOf(fieldValue.toString()));
            } else if ((Short.TYPE == fieldType)
                    || (Short.class == fieldType)) {
                field.set(o, Short.valueOf(fieldValue.toString()));
            } else if ((Double.TYPE == fieldType)
                    || (Double.class == fieldType)) {
                field.set(o, Double.valueOf(fieldValue.toString()));
            } else if (Character.TYPE == fieldType) {
                if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
                    field.set(o, fieldValue.toString().charAt(0));
                }
            } else if (Date.class == fieldType) {
                field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
            } else {
                field.set(o, fieldValue);
            }
        } else {
            throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }
    }

    /**
     * @param ws
     * @MethodName : setColumnAutoSize
     * @Description : 设置工作表自动列宽和首行加粗
     */
    public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
        //获取本列的最宽单元格的宽度
        for (int i = 0; i < ws.getColumns(); i++) {
            int colWith = 0;
            for (int j = 0; j < ws.getRows(); j++) {
                String content = ws.getCell(i, j).getContents().toString();
                int cellWith = content.length();
                if (colWith < cellWith) {
                    colWith = cellWith;
                }
            }
            //设置单元格的宽度为最宽宽度+额外宽度
            ws.setColumnView(i, colWith + extraWith);
        }
    }

    /**
     * @param sheet      工作表
     * @param list       数据源
     * @param fieldMap   中英文字段对应关系的Map
     * @param firstIndex 开始索引
     * @param lastIndex  结束索引
     * @MethodName : fillSheet
     * @Description : 向工作表中填充数据
     */
    public static <T> void fillSheet(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex, List appendSummaryList, Map<String, Exchange> exchange, ExportType exportType) throws Exception {
        //定义存放英文字段名和中文字段名的数组
        String[] enFields = new String[fieldMap.size()];
        String[] cnFields = new String[fieldMap.size()];
        //填充数组
        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            enFields[count] = entry.getKey();
            cnFields[count] = entry.getValue();
            count++;
        }
        Label label2 = new Label(0, 0, "序号");
        sheet.addCell(label2);
        //填充表头
        for (int i = 0; i < cnFields.length; i++) {
            Label label = new Label(i + 1, 0, cnFields[i]);
            sheet.addCell(label);
        }
        //填充内容
        int rowNo = 1;
        for (int index = firstIndex; index <= lastIndex; index++) {
            Label label3 = new Label(0, rowNo, rowNo + "");
            sheet.addCell(label3);
            //获取单个对象
            T item = list.get(index);
            for (int i = 0; i < enFields.length; i++) {
                Object objValue = getFieldValueByNameSequence(enFields[i], item);
                String fieldValue = objValue == null ? "" : objValue.toString();

                if (exchange != null && exchange.get(enFields[i]) != null) {
                    fieldValue = exchange.get(enFields[i]).exchange(fieldValue);
                }

                Label label = new Label(i + 1, rowNo, fieldValue);
                sheet.addCell(label);
            }
            rowNo++;
        }

        if (appendSummaryList != null && !appendSummaryList.isEmpty()) {
            appendSummaryValue(sheet, rowNo, appendSummaryList, exportType);
        }

        //设置自动列宽
        setColumnAutoSize(sheet, 5);
    }

    public static <T> void fillSheet4Header(WritableSheet sheet, LinkedHashMap<String, String> fieldMap) throws Exception {
        String[] fields = new String[fieldMap.size()];
        //填充数组
        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            fields[count] = entry.getValue();
            count++;
        }
        //填充表头
        for (int i = 0; i < fields.length; i++) {
            Label label = new Label(i, 0, fields[i]);
            sheet.addCell(label);
        }
    }

    public static <T> void fillSheet4Body(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int startRowNo) throws Exception {
        //定义存放英文字段名和中文字段名的数组
        String[] fields = new String[fieldMap.size()];
        //填充数组
        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            fields[count] = entry.getKey();
            count++;
        }
        //填充内容
        for (int index = 0; index < list.size(); index++) {
            //获取单个对象
            T item = list.get(index);
            for (int i = 0; i < fields.length; i++) {
                Object objValue = getFieldValueByNameSequence(fields[i], item);
                String fieldValue = objValue == null ? "" : objValue.toString();
                Label label = new Label(i, startRowNo, fieldValue);
                sheet.addCell(label);
            }
            startRowNo++;
        }
    }

    public static void appendSummaryValue(WritableSheet sheet, int rowNo, List appendSummaryList, ExportType exportType) {
        String[] appendTitleFields = null;
        if (null == exportType) {
            return;
        }
        if (exportType == ExportType.ADMIN_ORDER_SELECT) {
            appendTitleFields = new String[5];
            appendTitleFields[0] = "成功充值总笔数";
            appendTitleFields[1] = "成功充值总金额(元)";
            appendTitleFields[2] = "商户总收入(元)";
            appendTitleFields[3] = "代理商总收入(元)";
            appendTitleFields[4] = "平台总收入(元)";
//            appendTitleFields[5] = "渠道总收入(元)";
        } else if (exportType == ExportType.MERCHANT_ORDER_SELECT) {
            appendTitleFields = new String[4];
            appendTitleFields[0] = "成功充值总笔数";
            appendTitleFields[1] = "成功充值总金额(元)";
            appendTitleFields[2] = "商户总收入(元)";
            appendTitleFields[3] = "总手续费(元)";
        }else if (exportType == ExportType.PROXY_ORDER_SELECT) {
            appendTitleFields = new String[5];
            appendTitleFields[0] = "成功充值总笔数";
            appendTitleFields[1] = "成功充值总金额(元)";
            appendTitleFields[2] = "商户总收入(元)";
            appendTitleFields[3] = "总手续费(元)";
            appendTitleFields[4] = "代理商总收入(元)";
        }else if (exportType == ExportType.MERCHANT_SETTLE_LIST_D0) {
            appendTitleFields = new String[2];
            appendTitleFields[0] = "结算总笔数";
            appendTitleFields[1] = "结算总金额(元)";
        }else if (exportType == ExportType.MERCHANT_SETTLE_LIST_D1) {
            appendTitleFields = new String[3];
            appendTitleFields[0] = "结算总笔数";
            appendTitleFields[1] = "已结算总金额(元)";
            appendTitleFields[2] = "未结算总金额(元)";
        }else if (exportType == ExportType.MERCHANT_CASH_OUR_LIST) {
            appendTitleFields = new String[3];
            appendTitleFields[0] = "提现总笔数";
            appendTitleFields[1] = "提现总金额(元)";
            appendTitleFields[2] = "手续费(元)";
        }

        try {
            rowNo++;
            //追加汇总数据的表头
            for (int i = 0; i < appendTitleFields.length; i++) {
                Label label = new Label(i, rowNo, appendTitleFields[i]);
                sheet.addCell(label);
            }
            rowNo++;
            //填充内容
            for (int i = 0; i < appendTitleFields.length; i++) {
                //获取单个对象
                Label label = new Label(i, rowNo, appendSummaryList.get(i).toString());
                sheet.addCell(label);
            }
            rowNo++;
        } catch (WriteException e) {
            LogPortal.error("异常信息是:", e);
        }
    }

    public static void appendFooter(WritableSheet sheet, int rowNo, List footerTitleList, List summaryValueList) {
        try {
            rowNo++;
            if (footerTitleList != null && footerTitleList.size() > 0) {
                //追加汇总数据的表头
                for (int i = 0; i < footerTitleList.size(); i++) {
                    Label label = new Label(i, rowNo, footerTitleList.get(i).toString());
                    sheet.addCell(label);
                }
                rowNo++;
            }

            if (summaryValueList != null && summaryValueList.size() > 0) {
                //填充内容
                for (int i = 0; i < summaryValueList.size(); i++) {
                    //获取单个对象
                    Label label = new Label(i, rowNo, summaryValueList.get(i).toString());
                    sheet.addCell(label);
                }
                rowNo++;
            }

        } catch (WriteException e) {
            LogPortal.error("异常信息是:", e);
        }
    }

    public static int getRowsLength(InputStream inputStream) {
        int rows = 0;
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(0);
            rows = getRightRows(sheet);
        } catch (IOException e) {
            LogPortal.error("异常信息是:", e);
        } catch (BiffException e) {
            LogPortal.error("异常信息是:", e);
        }
        return rows;
    }

    private static int getRightRows(Sheet sheet) {
        int rsCols = sheet.getColumns(); //列数
        int rsRows = sheet.getRows(); //行数
        int nullCellNum;
        int afterRows = rsRows;
        for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数
            nullCellNum = 0;
            for (int j = 0; j < rsCols; j++) {
                String val = sheet.getCell(j, i).getContents();
                val = StringUtils.trimToEmpty(val);
                if (StringUtils.isBlank(val))
                    nullCellNum++;
            }
            if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数
                afterRows--; //行数减一
            }
        }
        return afterRows;
    }
}
